package org.UserHouse.Dao.Imp;

import org.UserHouse.Dao.HitemDao;
import org.UserHouse.Utils.Dbutils;
import org.UserHouse.entity.Hitem;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author bin
 * @date 2024-12-16
 */
public class HitemDaoImp implements HitemDao {


    @Override
    public List<Hitem> selectHitemPage(Hitem hitem, int page, int limit) {
        List<Hitem> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select * FROM ( ");
        sql.append("select  h_id,  h_name, t_id ,  h_price, h_grade,  h_content,  h_img,h_date, row_number() ");
        sql.append("over (order by h_date asc) as rownumber from h_item where 1=1 ");
        if (hitem != null && hitem.getHname() != null) {
            sql.append(" and h_name like ? ");
            params.add("%" + hitem.getHname() + "%");
        }
        sql.append(") as t2 where rownumber between ? and ? ");

        int startRow = (page - 1) * limit + 1;
        params.add(startRow);
        int endRow = (page * limit);
        params.add(endRow);

        PreparedStatement pst = null;
        ResultSet rst = null;
        Connection conn = Dbutils.getConnection();

        try {
            pst = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pst.setObject(i + 1, params.get(i));
            }
            rst = pst.executeQuery();
            while (rst.next()) {
                Hitem h = new Hitem();
                int hid = rst.getInt(1);
                String Hname = rst.getString(2);
                int Tid = rst.getInt(3);
                Double Hprice = rst.getDouble(4);
                String Hgarde = rst.getString(5);
                String Hcontent = rst.getString(6);
                String Himg = rst.getString(7);
                Date Hdate = rst.getDate(8);

                h.setHid(hid);
                h.setHname(Hname);
                h.setTid(Tid);
                h.setTprice(Hprice);
                h.setHgrade(Hgarde);
                h.setHcontent(Hcontent);
                h.setHimg(Himg);
                h.setHdate(Hdate);

                list.add(h);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public Long selectHitemCount(Hitem hitem) {
        long r = 0;
        StringBuilder sql = new StringBuilder("select COUNT(h_id) from h_item where 1=1 ");
        List<Hitem> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        if (hitem != null && hitem.getHname() != null) {
            sql.append(" and h_name like ? ");
            params.add("%" + hitem.getHname() + "%");
        }
        Connection conn = Dbutils.getConnection();
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            pst = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pst.setObject(i + 1, params.get(i));
            }
            rst = pst.executeQuery();
            if (rst.next()) {
                r = rst.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return r;
    }

    @Override
    public int AddHitem(Hitem hitem) {
        int n = 0;
        String sql = "insert into h_item ( h_name, t_id, h_price, h_grade, h_content, h_img) values(?,?,?,?,?,?)";
        PreparedStatement pst = null;
        ResultSet rst = null;
        Connection conn = Dbutils.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, hitem.getHname());
            pst.setInt(2, hitem.getTid());
            pst.setDouble(3, hitem.getTprice());
            pst.setString(4, hitem.getHgrade());
            pst.setString(5, hitem.getHcontent());
            pst.setString(6, hitem.getHimg());

            n = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(null, conn, pst);
        }
        return n;
    }

    @Override
    public int UpdateHitem(Hitem hitem) {
        int n = 0;
        String sql = "update h_item  set h_name=?, t_id=?, h_price=?, h_grade=?, h_content=?, h_img=? where h_id = ?";
        Connection conn  = Dbutils .getConnection() ;
        PreparedStatement pst =null;
        ResultSet rst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,hitem.getHname());
            pst.setInt(2,hitem.getTid());
            pst.setDouble(3,hitem.getTprice());
            pst.setString(4,hitem.getHgrade());
            pst.setString(5,hitem.getHcontent());
            pst.setString(6,hitem.getHimg());
            pst.setInt(7,hitem.getHid());
            n = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return n;
    }

    @Override
    public int DeleteHitem(int Hid) {
        int n = 0;
        String sql = "delete h_item  where h_id=? ";
        Connection conn = Dbutils.getConnection();
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, Hid);
            n = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return n;
    }
}
